﻿<cfsilent>
	<cfscript>
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		
		subjectId = event.getArg("SBJ");
		grade = event.getArg("GRD");
			
		/* 读取指定专业、年级待审查学位信息 */
		sql = "SELECT 
					a.date_created, a.date_review, 
					a.is_passed, a.comp_passed, review_passed, 
					b.stu_id, b.stu_name,
					c.credit, c.credit_grad, c.credit_grad_pt, c.credit_grad_avgpt,
					d.cls_name, 
					e.sbj_name, 
					f.institute_name, 
					g.tch_name 
				FROM t_degree_review a 
					INNER JOIN t_student b ON b.stu_id = a.stu_id 
					INNER JOIN t_student_profile c ON c.stu_id = a.stu_id 
					INNER JOIN t_class d ON d.cls_id = b.cls_id
					INNER JOIN t_subject e ON e.sbj_id = d.sbj_id
					INNER JOIN t_institute f ON f.institute_id = e.institute_id 
          			INNER JOIN t_teacher g ON g.tch_id = a.tch_id 
				WHERE
					d.grade = :grade 
					AND 
					d.sbj_id = :subjectId 
				ORDER BY 
					d.cls_name, b.stu_id ";
		
		queryObj = new Query( datasource=application.dnsSlave );
		
		queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );

		rs_summary = queryObj.execute( sql=sql ).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_summary.sbj_name & grade & "学位授予预审结果", "utf-8");
		
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("学位审核总览", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学院,专业,年级,班级,学号,姓名,总学分,学位课总学分,学位课绩点,学位课平均绩点,审核结论,自动审核,人工审核,初审时间,复审时间,审核人");
		
		/*添加数据*/
		for ( a=1; a LTE rs_summary.recordCount; a++){
			
			isPassed = ""; /* 毕业审核是否通过 */
			computerPassed = ""; /* 机器审核是否通过 */
			reviewPassed = ""; /* 人工审核是否通过 */
			
			if(rs_summary["is_passed"][a] EQ 1){
				isPassed = "Y";	
			}else{
				isPassed = "N";	
			}
			
			if(rs_summary["comp_passed"][a] EQ 1){
				compPassed = "Y";	
			}else{
				compPassed = "N";	
			}
			
			if(rs_summary["review_passed"][a] EQ 1){
				reviewPassed = "Y";	
			}else{
				reviewPassed = "N";	
			}
			
			 
	
			/* 构建计划信息 */
			rowData = rs_summary["institute_name"][a]
						& "," & rs_summary["sbj_name"][a]
						& "," & grade
						& "," & rs_summary["cls_name"][a]
						& "," & rs_summary["stu_id"][a]
						& "," & rs_summary["stu_name"][a]
						& "," & rs_summary["credit"][a]
						& "," & rs_summary["credit_grad"][a]
						& "," & rs_summary["credit_grad_pt"][a]
						& "," & rs_summary["credit_grad_avgpt"][a]
						& "," & isPassed 
						& "," & compPassed
						& "," & reviewPassed
						& "," & datetimeAdvice.formatDateString( left(rs_summary["date_created"][a],8) )
						& "," & datetimeAdvice.formatDateString( left(rs_summary["date_review"][a],8) )
						& "," & rs_summary["tch_name"][a];
			
			/* 写入计划信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
		}
		
		/* 创建审核明细 */
		excel.SpreadsheetCreateSheet (spreadsheetObj, "审核条件明细");
		excel.SpreadsheetSetActiveSheet (spreadsheetObj, "审核条件明细");
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学院,专业,年级,班级,学号,姓名,审核条件,是否通过,审核明细,审核人,审核时间");
		
		/*根据年级专业读取学位审核明细信息*/
		
		sql = "SELECT  
					d.cls_name,
        			b.stu_id,b.stu_name,
		    		a.is_passed, a.gdc_params, a.result_descript, a.date_created, 
					c.gdc_descript,
					e.sbj_name, 
					f.institute_name,
        			g.tch_name 
				FROM t_degree_details a 
          			INNER JOIN t_student b ON b.stu_id = a.stu_id 
					INNER JOIN t_grad_condition c ON c.gdc = a.gdc 
					INNER JOIN t_class d ON d.cls_id = b.cls_id
					INNER JOIN t_subject e ON e.sbj_id = d.sbj_id
					INNER JOIN t_institute f ON f.institute_id = e.institute_id 
          			INNER JOIN t_teacher g ON g.tch_id = a.tch_id 
        		WHERE
					d.grade = :grade 
					AND 
					d.sbj_id = :subjectId 
				ORDER BY 
					d.cls_name, b.stu_id ";
		
		queryObj = new Query( datasource=application.dnsSlave );
		
		queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );

		rs_detail = queryObj.execute( sql=sql ).getResult();
		
		/*添加数据*/
		for ( a=1; a LTE rs_detail.recordCount; a++){
			
			/*判断是否通过*/
			isPassed = "";
			
			if(rs_detail["is_passed"][a] EQ 1){
				isPassed = "Y";	
			}else{
				isPassed = "N";	
			}
			
			/* 构建计划信息 */
			rowData = rs_detail["institute_name"][a]
						& "," & rs_detail["sbj_name"][a]
						& "," & grade
						& "," & rs_detail["cls_name"][a]
						& "," & rs_detail["stu_id"][a]
						& "," & rs_detail["stu_name"][a]
						& "," & rs_detail["gdc_descript"][a]
						& "," & isPassed
						& "," & replace(rs_detail["result_descript"][a],",","，","ALL")
						& "," & rs_detail["tch_name"][a]
						& "," & left(rs_detail["date_created"][a],8);
			
			/* 写入计划信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
		}
		
		
		/* 创建学生未通过学位课 */
		excel.SpreadsheetCreateSheet (spreadsheetObj, "未通过学位课");
		excel.SpreadsheetSetActiveSheet (spreadsheetObj, "未通过学位课");
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学院,专业,年级,班级,学号,姓名,课程名称,课程学分");
		
		/*读取未通过学位课程*/
		sql = "SELECT a.stu_id, e.institute_name,
					  c.cls_name, a.stu_name, d.sbj_name, 
					  g.course_name, g.course_credit    
				FROM t_mark_need t 
             		 INNER JOIN t_student a ON a.stu_id = t.stu_id  
					 INNER JOIN t_class c ON c.cls_id = a.cls_id 
					 INNER JOIN t_subject d ON d.sbj_id = c.sbj_id 
					 INNER JOIN t_institute e ON e.institute_id = d.institute_id 
				     INNER JOIN t_course g ON g.cid = t.cid 
				WHERE 
					  (
						 t.gdc = 'CPD' 
						 OR 
						 t.gdc = 'CBD'  
					  )
					  AND 
					  c.grade = :grade  
					  AND 
				      c.sbj_id = :subjectId 
				ORDER BY 
					  c.cls_name, a.stu_id";
							  
		queryObj = new Query( datasource=application.dnsSlave );

		queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
		
		rs_student_required = queryObj.execute( sql=sql ).getResult();
		
		/*添加数据*/
		for ( a=1; a LTE rs_student_required.recordCount; a++){
			
			/* 构建计划信息 */
			rowData = rs_student_required["institute_name"][a]
						& "," & rs_student_required["sbj_name"][a]
						& "," & grade
						& "," & rs_student_required["cls_name"][a]
						& "," & rs_student_required["stu_id"][a]
						& "," & rs_student_required["stu_name"][a]
						& "," & rs_student_required["course_name"][a]
						& "," & rs_student_required["course_credit"][a];
			
			/* 写入计划信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
		}
		
		excel.SpreadsheetSetActiveSheet (spreadsheetObj, "学位审核总览");
		
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	

</cfsilent>